package com.example.mapper;

import com.example.pojo.Bars;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.pojo.Users;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.web.bind.annotation.RequestParam;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author ${author}
 * @since 2024-06-06
 */
public interface BarsMapper extends BaseMapper<Bars> {

    @Select("SELECT \n" +
            "    b.*, \n" +
            "    t.tname, \n" +
            "    e.level,\n" +
            "    e.levelname, \n" +
            "    (SELECT count(*) FROM follow WHERE bid = b.bid) AS fc,\n" +
            "    (SELECT count(*) FROM posts WHERE bid = b.bid) AS pc\n" +
            "FROM \n" +
            "    bars b \n" +
            "JOIN \n" +
            "    post_types t ON b.tid = t.tid \n" +
            "JOIN \n" +
            "    ed e ON b.ex >= e.ex\n" +
            "WHERE \n" +
            "    NOT EXISTS (\n" +
            "        SELECT 1\n" +
            "        FROM ed e2\n" +
            "        WHERE b.ex >= e2.ex AND e2.ex > e.ex\n" +
            "    )")
    List<Bars> getAllBars();

    //单独id的
    @Select("SELECT \n" +
            "    b.*, \n" +
            "    t.tname, \n" +
            "    e.level,\n" +
            "\t\te.levelname, \n" +
            "    (SELECT count(*) FROM follow WHERE bid = b.bid) AS fc,\n" +
            "    (SELECT count(*) FROM posts WHERE bid = b.bid) AS pc\n" +
            "FROM \n" +
            "    bars b \n" +
            "JOIN \n" +
            "    post_types t ON b.tid = t.tid \n" +
            "JOIN \n" +
            "    ed e ON b.ex >= e.ex\n" +
            "WHERE \n" +
            "    NOT EXISTS (\n" +
            "        SELECT 1\n" +
            "        FROM ed e2\n" +
            "        WHERE b.ex >= e2.ex AND e2.ex > e.ex\n" +
            "    )     \n" +
            "AND b.bid = #{bid}")
    List<Bars> getAllBarsByBid(@RequestParam("bid") Integer bid);

    @Select({
            "<script>",
            "SELECT * FROM users WHERE uid IN",
            "<foreach item='id' collection='idList' open='(' separator=',' close=')'>",
            "#{id}",
            "</foreach>",
            "</script>"
    })
    List<Users> getUser(@Param("idList") List<Integer> idList);

    @Update("update bars set status=#{status} where bid = #{bid}")
    void StatusChange(int status,int bid);


    @Select("select b.*,t.tname,e.* from bars b join post_types t on b.tid = t.tid join ed  e ON b.ex >= e.ex\n" +
            "        AND\n" +
            "            NOT EXISTS (\n" +
            "                SELECT 1\n" +
            "                FROM ed e2\n" +
            "                WHERE b.ex >= e2.ex AND e2.ex > e.ex\n" +
            "            )\n" +
            "WHERE 1=1\n" +
            "\t\t\t\t\t\t\tAND ${select} LIKE concat('%', #{input}, '%')\n" +
            "\t\t\t\t\t\t\t\t\n" +
            "\t\t\t\t\t\t\tAND NOT EXISTS (\n" +
            "                SELECT 1\n" +
            "                FROM ed e2\n" +
            "                WHERE b.ex >= e2.ex AND e2.ex > e.ex\n" +
            "            )")
    List<Bars> getFindBars(@Param("select") String select,@Param("input") String input);

    @Select("SELECT \n" +
            "    b.*, \n" +
            "    t.tname, \n" +
            "    e.level,\n" +
            "\t\te.levelname, \n" +
            "    (SELECT count(*) FROM follow WHERE bid = b.bid) AS fc,\n" +
            "    (SELECT count(*) FROM posts WHERE bid = b.bid) AS pc\n" +
            "FROM \n" +
            "    bars b \n" +
            "JOIN \n" +
            "    post_types t ON b.tid = t.tid \n" +
            "JOIN \n" +
            "    ed e ON b.ex >= e.ex\n" +
            "WHERE \n" +
            "    NOT EXISTS (\n" +
            "        SELECT 1\n" +
            "        FROM ed e2\n" +
            "        WHERE b.ex >= e2.ex AND e2.ex > e.ex\n" +
            "    )     \n" +
            "AND b.tid = #{tid}")
    List<Bars> FindBar(@RequestParam("tid") Integer tid);

    @Select("SELECT \n" +
            "    b.*, \n" +
            "    t.tname, \n" +
            "    e.level,\n" +
            "\t\te.levelname, \n" +
            "    (SELECT count(*) FROM follow WHERE bid = b.bid) AS fc,\n" +
            "    (SELECT count(*) FROM posts WHERE bid = b.bid) AS pc\n" +
            "FROM \n" +
            "    bars b \n" +
            "JOIN \n" +
            "    post_types t ON b.tid = t.tid \n" +
            "JOIN \n" +
            "    ed e ON b.ex >= e.ex\n" +
            "WHERE \n" +
            "    NOT EXISTS (\n" +
            "        SELECT 1\n" +
            "        FROM ed e2\n" +
            "        WHERE b.ex >= e2.ex AND e2.ex > e.ex\n" +
            "    )     \n" +
            "AND b.bid IN(select b.bid from bars b where bid IN(\n" +
            "\tselect coalesce(bid,uid)\n" +
            "\tfrom follow where uid =#{uid}\n" +
            "))")
    List<Bars> userFindBar(@RequestParam("uid") Integer uid);

    //-================================================================管理中心

    @Select("SELECT *\n" +
            "FROM bars\n" +
            "WHERE uid = #{uid}  -- 查找 uid 等于 1001 的记录\n" +
            "   OR FIND_IN_SET(#{uid}, REPLACE(otherid, ';', ',')) > 0  -- 查找 otherid 中含有 1001 的记录\n" +
            "ORDER BY \n" +
            "  CASE \n" +
            "    WHEN uid = #{uid} THEN 0  -- uid 为 1001 的记录排在前面\n" +
            "    ELSE 1  -- 其他记录排在后面\n" +
            "  END;")
    List<Bars> getBarsCenter(@RequestParam("uid") Integer uid);

    @Select("select a.*,u.name,u.phone,u.image,u.gender,d.*,b.bname,b.bimage from apply a join users u on a.uid = u.uid \n" +
            "\t\tjoin user_details d on d.uid = a.uid \n" +
            "\t\tjoin bars b on a.bid = b.bid \n" +
            "\t\twhere a.bid = #{bid}\n")
    List<Bars> getAllApply(@RequestParam("bid") Integer bid);

    @Select("select * from bars where uid = #{uid}")
    List<Bars> getMyBars(@RequestParam("uid") Integer uid);

    @Insert("insert into bars (bname,uid,otherid,status,tid,ex,bdata,bimage) values (#{bname},#{uid},null,0,#{tid},2,#{bdata},#{bimage})")
    void addBars(@RequestParam("uid") Integer uid,@RequestParam("bname") String bname,@RequestParam("tid") Integer tid,@RequestParam("bimage") String bimage,String bdata);

    @Update("UPDATE bars " +
            "SET otherid = TRIM(BOTH ',' FROM REPLACE(CONCAT_WS(',', " +
            "                      REPLACE(otherid, #{uid}, ''), ''), ',,', ',')) " +
            "WHERE otherid LIKE CONCAT('%', #{uid}, '%') AND bid = #{bid}")
    void delOtherid(Integer uid, Integer bid);

    @Update("update apply set status = 0 where uid = #{uid} and bid= #{bid}")
    void updateApply(@RequestParam("uid") Integer uid,@RequestParam("bid") Integer bid);

    @Update("update bars set otherid = #{otherid} where bid = #{bid}")
    void updateBarsBybid(@RequestParam("bid") Integer bid,@RequestParam("otherid") String otherid);
}
